#!/bin/bash
LOG_MYSQL_USER="yw"
LOG_MYSQL_PWD="yw"

LOG_SERVER_USER="zybzhao"
LOG_SERVER_PWD="rWgCT#7Y"

LOCALSVR="zybzhao@172.25.42.74"
LOCALPASSMY="zyb"

HOSTFILE='host.xml'

EXEC_EXPECT_SSH_COMMON="set timeout 60000; expect \"*assword:*\" { send \"${LOG_SERVER_PWD}\\r\" } \"yes/no\" { send \"yes\\r\"; expect \"*assword:*\" { send \"${LOG_SERVER_PWD}\\r\" } } ; expect eof;"

EXEC_EXPECT_SCP_COMMON="set timeout 60000; expect \"*assword:*\" { send \"${LOG_SERVER_PWD}\\r\" } \"yes/no\" { send \"yes\\r\" ; expect \"*assword:*\" { send \"${LOG_SERVER_PWD}\\r\" } } ; expect \"*assword:*\" { send \"${LOCALPASSMY}\\r\" } \"yes/no\" { send \"yes\\r\" ; expect \"*assword:*\" send { \"${LOCALPASSMY}\\r\" } } ;expect eof;"

declare -a ARRAY_WORLDID;
declare -a ARRAY_WORLDIP;

SQL_SENCENTCE=$(cat ./extract_log.sql)
RESULT_FILENAME="SQL_RESULT_WORLD";

function exec_mysql()
{
	local DBWORLDID=$1
	local LOG_DB_DEST_IP=$2
	local SQL_RESULT_FILE_NAME="${RESULT_FILENAME}${DBWORLDID}.txt"
	local SQL_RESULT_DST_TMP_PATH="/tmp/${SQL_RESULT_FILE_NAME}"
	local DB_NAME="${DBWORLDID}_dwc_logdb"
	#delete exist files
	echo "delete exist files"
	local EXEC_ROMOTE="test -e ${SQL_RESULT_DST_TMP_PATH} && rm -fr ${SQL_RESULT_DST_TMP_PATH};"
	local EXEC="ssh ${LOG_SERVER_USER}@${LOG_DB_DEST_IP} -q \"${EXEC_ROMOTE}\"  ${EXEC_EXPECT_SSH_COMMON}"
	expect -c "spawn -noecho ${EXEC}"

	#login on server delete exsitfile
	echo " LogOnSver worldid@${DBWORLDID} ip@${LOG_DB_DEST_IP}";
	EXEC_ROMOTE="mysql -u${LOG_MYSQL_USER} -p${LOG_MYSQL_PWD} -D ${DB_NAME} --connect_timeout=300 -e \\\"${SQL_SENCENTCE}\\\" >> ${SQL_RESULT_DST_TMP_PATH};"
	EXEC="ssh ${LOG_SERVER_USER}@${LOG_DB_DEST_IP} -q \"${EXEC_ROMOTE}\"  ${EXEC_EXPECT_SSH_COMMON}";
	expect -c "set timeout -1;spawn -noecho ${EXEC}"

	#copy result to jump machine 
	echo "copy result to jump machine";
	EXEC="scp -q ${LOG_SERVER_USER}@${LOG_DB_DEST_IP}:${SQL_RESULT_DST_TMP_PATH} ./${SQL_RESULT_FILE_NAME}; ${EXEC_EXPECT_SSH_COMMON}";
	expect -c "spawn -noecho ${EXEC}"

	#delete exist files
	echo "delete exist files"
	EXEC_ROMOTE="test -e ${SQL_RESULT_DST_TMP_PATH} && rm -fr ${SQL_RESULT_DST_TMP_PATH};"
	EXEC="ssh ${LOG_SERVER_USER}@${LOG_DB_DEST_IP} -q \"${EXEC_ROMOTE}\"  ${EXEC_EXPECT_SSH_COMMON}"
	expect -c "spawn -noecho ${EXEC}"
}

function parse_host()
{
	echo 'parse host'
	local I=0;
	while row=$(line)
	do
		local LOG_LINE=$( echo $row | egrep "w([0-9]+)_log");
		if [ -n "$LOG_LINE" ]; then 
			echo "${I}"
			local WORLDID=$(echo ${LOG_LINE}|egrep "w[0-9]+_log" -o|egrep "([0-9])+" -o);
			local WORLDDBIP=$(echo ${LOG_LINE}|egrep "InnerIP=\"(.*)\"" -o|egrep "[0-9].+[0-9]" -o);
			
 			ARRAY_WORLDID[$I]=${WORLDID};
			ARRAY_WORLDIP[$I]=${WORLDDBIP};
			I=$((${I}+1));
		fi
	done < ${HOSTFILE}
}

function merge_files()
{
	#remove exsit file
	test -e ${RESULT_FILENAME} && rm -fr ${RESULT_FILENAME};
	local num=${#ARRAY_WORLDID[@]};
	echo $num;
	for((i=0;i<$num;i=i+1))
	do
		echo "world_${ARRAY_WORLDID[$i]} start" >> ${RESULT_FILENAME};
		cat "${RESULT_FILENAME}${ARRAY_WORLDID[$i]}.txt" >> ${RESULT_FILENAME};
		echo "---------------------------------------------------------------------" >> ${RESULT_FILENAME};
	done
}
function start()
{
	parse_host;	

	local num=${#ARRAY_WORLDID[@]};
	for((i=0;i<$num;i=i+1))
	do
		exec_mysql ${ARRAY_WORLDID[$i]} ${ARRAY_WORLDIP[$i]}; 
	done

	merge_files;
}

start;


